package com.yjm.test.Spring5.ch6.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.yjm.test.Spring5.ch6.dao.SingerDao;
import com.yjm.test.Spring5.ch6.entities.Singer;


public class PlainSingerDao implements SingerDao {
	private static Logger logger = LoggerFactory.getLogger(PlainSingerDao.class);
	static {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException ex ) {
			logger.error("problem loading DB Driver!",  ex);
		}
	}
	
	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:mysql://192.168.18.173:3306/musicdb","prospring5", "123456");
	}
	private void closeConnection(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				logger.error("Problem closing connection to the database!", e);
			}
		}
	}
	
	@Override
	public List<Singer> findAll() {
		List<Singer> result = new ArrayList<>();
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement statement = connection.prepareStatement("select * from SINGER");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Singer singer = new Singer();
				singer.setId(resultSet.getLong("id"));
				singer.setFirstName(resultSet.getString("first_name"));
				singer.setLastName(resultSet.getString("last_name"));
				singer.setBirthDate(resultSet.getDate("birth_date"));
				
				result.add(singer);
			}
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
		return result;
	}

	@Override
	public List<Singer> findByFirstName(String firstName) {
		List<Singer> result = new ArrayList<>();
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement statement = connection.prepareStatement("select * from SINGER where first_name=?");
			statement.setString(1, firstName);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Singer singer = new Singer();
				singer.setId(resultSet.getLong("id"));
				singer.setFirstName(resultSet.getString("first_name"));
				singer.setLastName(resultSet.getString("last_name"));
				singer.setBirthDate(resultSet.getDate("birth_date"));
				
				result.add(singer);
			}
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
		return result;
	}

	@Override
	public String findLastNameById(Long id) {
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement statement = connection.prepareStatement("select last_name from SINGER where id=?");
			statement.setLong(1, id);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				String name = resultSet.getString("last_name");
				return name;
			}
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
		return null;
	}

	@Override
	public String findFirstNameById(Long id) {
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement statement = connection.prepareStatement("select first_name from SINGER where id=?");
			statement.setLong(1, id);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				String name = resultSet.getString("first_name");
				return name;
			}
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
		return null;
	}

	@Override
	public void insert(Singer singer) {
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement statement = connection.prepareStatement("insert into SINGER(first_name,last_name,birth_date) value(?,?,?)", Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, singer.getFirstName());
			statement.setString(2, singer.getLastName());
			statement.setDate(3, (Date) singer.getBirthDate());
			statement.execute();
			ResultSet generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				singer.setId(generatedKeys.getLong(1));
			}
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
	}

	@Override
	public boolean update(Singer singer) {
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement statement = connection.prepareStatement("update SINGER set first_name=?,last_name=?,birth_date=? where id=?");
			statement.setString(1, singer.getFirstName());
			statement.setString(2, singer.getLastName());
			statement.setDate(3, new Date(singer.getBirthDate().getTime()));
			statement.setLong(4, singer.getId());
			int rows = statement.executeUpdate();
			if (rows > 0) {
				return true;
			}
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
		return false;
	}

	@Override
	public boolean delete(Long singerId) {
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement statement = connection.prepareStatement("delete from SINGER where id=?");
			statement.setLong(1, singerId);
			int rows = statement.executeUpdate();
			if (rows > 0) {
				return true;
			}
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
		return false;
	}

	@Override
	public List<Singer> findAllWithDetail() {
		List<Singer> result = new ArrayList<>();
		Connection connection = null;
		try {
			connection = getConnection();
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
		return null;
	}

	@Override
	public void insertWithDetail(Singer singer) {
		Connection connection = null;
		try {
			connection = getConnection();
		} catch (SQLException e) {
			logger.error("Problem when executing select!", e);
		} finally {
			closeConnection(connection);
		}
	}
	
	
	public static void main(String[] args) {
		SingerDao singerDao = new PlainSingerDao();
		
		listAllSingers(singerDao);

		logger.info("------------");
		logger.info("insert a new singer");
		Singer singer = new Singer();
		singer.setFirstName("Bryan");
		singer.setLastName("yan");
		singer.setBirthDate(new Date(new GregorianCalendar(1987,1,1).getTime().getTime()));
		singerDao.insert(singer);
		logger.info("Listing singer data after new singer created");
		listAllSingers(singerDao);
		logger.info("------------");
		logger.info("update the new singer");
		singer.setLastName("yjm");
		singerDao.update(singer);
		logger.info("Listing singer data after new singer update");
		listAllSingers(singerDao);
		logger.info("------------");
		logger.info("delete the new singer");
		singerDao.delete(singer.getId());
		logger.info("Listing singer data after new singer deleted");
		listAllSingers(singerDao);
		logger.info("------------");
	}

	private static void listAllSingers(SingerDao singerDao) {
		List<Singer> singers = singerDao.findAll();
		for (Singer singer:singers) {
			logger.info(singer.toString());
		}
	}
}
